home *** CD-ROM | disk | FTP | other *** search
/ Object Oriented Applicat…isualAge for C++ for OS/2 / Object-Oriented Application Development with VisualAGE for C++ for OS2.iso / creatab.ddl next >
Text File  |  1996-02-26  |  5KB  |  177 lines
  1.  
  2.  CREATE TABLE USERID.BUYER
  3.       (BUYER_ID CHAR(11) NOT NULL,
  4.        FIRST_NAME CHAR(20) NOT NULL,
  5.        LAST_NAME CHAR(20) NOT NULL,
  6.        MARRITAL_STATUS CHAR(1),
  7.        GENDER CHAR(1),
  8.        INCOME DECIMAL(15,2),
  9.        WORK_PHONE CHAR(12),
  10.        HOME_PHONE CHAR(12),
  11.        PRIMARY KEY (BUYER_ID));
  12.  
  13.  CREATE TABLE USERID.BUYER_ADDRESS
  14.       (ADDRESS_ID CHAR(11) NOT NULL,
  15.        STREET CHAR(40) NOT NULL,
  16.        AREA CHAR(40) NOT NULL,
  17.        CITY CHAR(40) NOT NULL,
  18.        STATE CHAR(2),
  19.        ZIP_CODE CHAR(5) FOR BIT DATA,
  20.        PRIMARY KEY ( ADDRESS_ID ));
  21.  
  22.  CREATE TABLE USERID.BUYER_LOG
  23.        (BUYER_ID CHAR(11) NOT NULL,
  24.         CREATION_TIMESTAMP TIMESTAMP,
  25.         LAST_UPDATE TIMESTAMP,
  26.         PRIMARY KEY ( BUYER_ID ));
  27.  
  28.  CREATE TABLE USERID.MARKETING_INFO
  29.        (PROPERTY_ID CHAR(5) NOT NULL,
  30.         PRICE DECIMAL(7,0) NOT NULL,
  31.         DAYS_ON_MARKET SMALLINT,
  32.         COMMISSION_RATE DECIMAL(5,2),
  33.         DOWN_PAYMENT_RATE DECIMAL(5,2),
  34.         PRIMARY KEY ( PROPERTY_ID ));
  35.  
  36.  CREATE TABLE USERID.MULTIDOC
  37.        (MULTIDOC_ID CHAR(5) NOT NULL,
  38.         FILENAME VARCHAR(254) NOT NULL,
  39.         TYPE CHAR(20) NOT NULL,
  40.         PRIMARY KEY (MULTIDOC_ID));
  41.  
  42.  CREATE TABLE USERID.PREFERENCE
  43.        (BUYER_ID CHAR(11) NOT NULL,
  44.         MAX_PRICE DECIMAL(15,2),
  45.         MIN_PRICE DECIMAL(15,2),
  46.         MAX_SIZE DECIMAL(15,2),
  47.         MIN_SIZE DECIMAL(15,2),
  48.         BEDROOMS SMALLINT,
  49.         BATHROOMS SMALLINT,
  50.         STORIES SMALLINT,
  51.         HEATING CHAR(30),
  52.         COOLING CHAR(30),
  53.         PRIMARY KEY (BUYER_ID));
  54.  
  55.  CREATE TABLE USERID.PROPERTY
  56.        (PROPERTY_ID CHAR(5) NOT NULL,
  57.         SIZE DECIMAL(5,0) NOT NULL,
  58.         BEDROOMS SMALLINT NOT NULL,
  59.         BATHROOMS SMALLINT NOT NULL,
  60.         STORIES SMALLINT NOT NULL,
  61.         COOLING CHAR(30) NOT NULL,
  62.         HEATING CHAR(30) NOT NULL,
  63.         DESCRIPTION VARCHAR(512),
  64.         PRIMARY KEY (PROPERTY_ID));
  65.  
  66.  CREATE TABLE USERID.PROPERTY_ADDRESS
  67.        (ADDRESS_ID CHAR(5) NOT NULL,
  68.         STREET CHAR(40) NOT NULL,
  69.         AREA CHAR(40),
  70.         CITY CHAR(40) NOT NULL,
  71.         STATE CHAR(2),
  72.         ZIP_CODE CHAR(5) FOR BIT DATA,
  73.         PRIMARY KEY (ADDRESS_ID));
  74.  
  75.  CREATE TABLE USERID.PROPERTY_LOG
  76.        (PROPERTY_ID CHAR(5) NOT NULL,
  77.         DOWNLOAD_TIMESTAMP TIMESTAMP NOT NULL,
  78.         LAST_UPDATE TIMESTAMP NOT NULL,
  79.         STATUS CHAR(15) NOT NULL,
  80.         PRIMARY KEY (PROPERTY_ID));
  81.  
  82.  CREATE TABLE USERID.SALE_TRANSACTION
  83.        (TRANSACTION_ID TIMESTAMP NOT NULL,
  84.         LAST_UPDATE TIMESTAMP NOT NULL,
  85.         AGREEMENT_FORM_ID INTEGER NOT NULL,
  86.         STATUS CHAR(10) NOT NULL,
  87.         BUYER_ID CHAR(11) NOT NULL,
  88.         PROPERTY_ID CHAR(5) NOT NULL,
  89.         PRIMARY KEY (TRANSACTION_ID));
  90.  
  91.  CREATE VIEW USERID.BUYER_INFO
  92.        (BUYER_ID,
  93.         FIRST_NAME,
  94.         LAST_NAME,
  95.         INCOME,
  96.         WORK_PHONE,
  97.         HOME_PHONE,
  98.         STREET,
  99.         AREA,
  100.         CITY,
  101.         STATE,
  102.         ZIP_CODE,
  103.         MAX_PRICE,
  104.         MIN_PRICE,
  105.         MAX_SIZE,
  106.         MIN_SIZE,
  107.         BEDROOMS,
  108.         BATHROOMS,
  109.         STORIES,
  110.         HEATING,
  111.         COOLING)
  112.         AS SELECT A.BUYER_ID,
  113.                   FIRST_NAME,
  114.                   LAST_NAME,
  115.                   INCOME,
  116.                   WORK_PHONE,
  117.                   HOME_PHONE,
  118.                   STREET,
  119.                   AREA,
  120.                   CITY,
  121.                   STATE,
  122.                   ZIP_CODE,
  123.                   MAX_PRICE,
  124.                   MIN_PRICE,
  125.                   MAX_SIZE,
  126.                   MIN_SIZE,
  127.                   BEDROOMS,
  128.                   BATHROOMS,
  129.                   STORIES,
  130.                   HEATING,
  131.                   COOLING
  132.                   FROM USERID.BUYER A,
  133.                   USERID.BUYER_ADDRESS B,
  134.                   USERID.PREFERENCE C
  135.                   WHERE (A.BUYER_ID=ADDRESS_ID
  136.                          AND A.BUYER_ID=C.BUYER_ID);
  137.  
  138.  CREATE VIEW USERID.PROP_AD_LOG
  139.        (PROPERTY_ID,
  140.         SIZE,
  141.         BEDROOMS,
  142.         BATHROOMS,
  143.         AREA,
  144.         CITY,
  145.         STATE,
  146.         STATUS,
  147.         PRICE,
  148.         COMMISSION_RATE,
  149.         DOWN_PAYMENT_RATE)
  150.         AS SELECT A.PROPERTY_ID,
  151.                   SIZE,
  152.                   BEDROOMS,
  153.                   BATHROOMS,
  154.                   AREA,
  155.                   CITY,
  156.                   STATE,
  157.                   STATUS,
  158.                   PRICE,
  159.                   COMMISSION_RATE,
  160.                   DOWN_PAYMENT_RATE
  161.                   FROM USERID.PROPERTY A,
  162.                        USERID.PROPERTY_ADDRESS B,
  163.                        USERID.PROPERTY_LOG C,
  164.                        USERID.MARKETING_INFO D
  165.                   WHERE (A.PROPERTY_ID=ADDRESS_ID AND
  166.                          A.PROPERTY_ID=C.PROPERTY_ID AND
  167.                          A.PROPERTY_ID=D.PROPERTY_ID);
  168.  
  169.  CREATE VIEW USERID.LIST_AREA
  170.        (AREA)
  171.         AS SELECT DISTINCT AREA
  172.                   FROM USERID.PROPERTY_ADDRESS A,
  173.                        USERID.PROPERTY_LOG B
  174.                   WHERE (ADDRESS_ID=PROPERTY_ID) AND
  175.                         ((STATUS='AVAILABLE'));
  176.  
  177.